﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using RTSoft.Modules;

namespace RTSoft.SoureCode.Control.HangHoa
{
    class hanghoactrl
    {
        DataProvider provider = new DataProvider();
        SqlCommand cmd = new SqlCommand();
        String strSQL = "";
        SqlDataReader reader;

        public void getTop10Item(ref String errConnect, ref String errSQL, ref DataTable tbl,DateTime start, DateTime end)
        {
            cmd.Connection = provider.Connection(ref errConnect);
            try
            {
                tbl = new DataTable();
                strSQL = "GetTop10Item";    
                cmd.CommandText = strSQL;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Start", start);
                cmd.Parameters.AddWithValue("@End", end);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                tbl = ds.Tables[0];
            }
            catch (Exception ex)
            {
                errSQL = ex.Message;
            }
            finally
            {
                provider.Disconnect();
            }
        }
        public void getDataFilter(ref String errConnect, ref String errSQL, ref DataTable tbl)
        {
            provider.Connection(ref errConnect);
            try
            {
                tbl = new DataTable();
                strSQL = "select a.ID_MATHANG,TEN_MATHANG,ID_LOAI,ID_XX,ID_NSX,DONVITINH,VAT,SOLUONG_MAX,SOLUONG_MIN,TRANGTHAI,isnull(b.SOLUONG,0) as SOLUONG,isnull(b.THANHTIEN,0) as THANHTIEN from HANGHOA a left join ";
                strSQL += " (select ID_MATHANG,SUM(SOLUONG_BAN)as SOLUONG ,SUM(a.THANHTIEN) as THANHTIEN from ChiTiet_HD a, HoaDon b where a.ID_HD = b.ID_HD Group by ID_MATHANG) b on a.ID_MATHANG = b.ID_MATHANG  order by THANHTIEN DESC"; 
                tbl = provider.LayDanhSach(strSQL);
            }
            catch (Exception ex)
            {
                errSQL = ex.Message;
            }
            finally
            {
                provider.Disconnect();
            }
        }

        public void getData(ref String errConnect, ref String errSQL, ref DataTable tbl)
        {
            provider.Connection(ref errConnect);
            try
            {
                tbl = new DataTable();
                strSQL = "select ID_MATHANG,TEN_MATHANG,ID_LOAI,ID_XX,ID_NSX,DONVITINH,VAT,SOLUONG_MAX,SOLUONG_MIN,TRANGTHAI from HANGHOA Order by SOLUONG_MIN DESC ";
                tbl = provider.LayDanhSach(strSQL);
            }
            catch (Exception ex)
            {
                errSQL = ex.Message;
            }
            finally
            {
                provider.Disconnect();
            }
        }

        public void getDoanhThu(ref String errConnect, ref String errSQL, ref DataTable tbl)
        {
            provider.Connection(ref errConnect);
            try
            {
                tbl = new DataTable();
                strSQL = "select a.ID_MATHANG,TEN_MATHANG,ID_LOAI,ID_XX,ID_NSX,DONVITINH,VAT,SOLUONG_MAX,SOLUONG_MIN,SOLUONG_MAX-SOLUONG_MIN as SLBAN,isnull(b.DONGIA,0) as DONGIA, isnull(((SOLUONG_MAX-SOLUONG_MIN) * DONGIA),0) as DOANHTHU,TRANGTHAI from HANGHOA a,DONGIA_BAN b ";
                strSQL += " where a.ID_MATHANG = b.ID_MATHANG Order by DOANHTHU DESC ";
                tbl = provider.LayDanhSach(strSQL);
            }
            catch (Exception ex)
            {
                errSQL = ex.Message;
            }
            finally
            {
                provider.Disconnect();
            }
        }

        public int getQuantity(ref String errConnect, ref String errSQL, int ID_MATHANG)
        {
            int price = 0;
            try
            {
                cmd.Connection = provider.Connection(ref errConnect);
                strSQL = "select SOLUONG_MIN from HANGHOA WHERE ID_MATHANG = @ID_MATHANG";
                cmd.CommandText = strSQL;
                cmd.Parameters.AddWithValue("@ID_MATHANG", ID_MATHANG);
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    price = Convert.ToInt32(reader[0]);
                }
            }
            catch (Exception ex)
            {
                errSQL = ex.Message;
            }
            finally
            {
                provider.Disconnect();
            }
            return price;
        }


        
        public hanghoainfo getItembyID(ref String errConnect, ref String errSQL, int id)
        {
            hanghoainfo info = new hanghoainfo();
            cmd.Connection = provider.Connection(ref errConnect);
            try
            {
                cmd.CommandText = "select * from HANGHOA where ID_MATHANG =" + id;
                reader = cmd.ExecuteReader();
                while (reader.Read()) {
                    info.ID_MATHANG = Convert.ToInt32(reader["ID_MATHANG"]);
                    info.ID_LOAI = Convert.ToInt32(reader["ID_LOAI"]);
                    info.ID_NSX = Convert.ToInt32(reader["ID_NSX"]);
                    info.ID_XX = Convert.ToInt32(reader["ID_XX"]);
                    info.TEN_MATHANG = reader["TEN_MATHANG"].ToString();
                    info.SOLUONG_MAX = Convert.ToInt32(reader["SOLUONG_MAX"]);
                    info.SOLUONG_MIN = Convert.ToInt32(reader["SOLUONG_MIN"]);
                    info.VAT = Convert.ToInt32(reader["VAT"]);
                    info.DONVITINH = reader["DONVITINH"].ToString();
                }
            }
            catch (Exception ex)
            {
                errSQL = ex.Message;
            }
            return info;
        }

        public void getDataBySelect(ref String errConnect, ref String errSQL, ref DataTable tbl, String select)
        {
            provider.Connection(ref errConnect);
            try
            {
                tbl = new DataTable();
                strSQL = "select "+select+" from HANGHOA Where SOLUONG_MIN > 0 order by SOLUONG_MIN DESC";
                tbl = provider.LayDanhSach(strSQL);
            }
            catch (Exception ex)
            {
                errSQL = ex.Message;
            }
            finally
            {
                provider.Disconnect();
            }
        }

        public void Insert(ref String errConnect, ref String errSQL, hanghoainfo info)
        {
            cmd.Connection = provider.Connection(ref errConnect);
            if (errConnect != "")
            {
                return;
            }
            cmd.Transaction = provider.BeginTransaction();
            try
            {
                SqlParameter f = new SqlParameter();
                f.IsNullable = true;
                f.Value = System.DBNull.Value;
                strSQL = "Insert into HANGHOA";
                strSQL = strSQL + "(ID_NSX,ID_XX,ID_LOAI,TEN_MATHANG,DONVITINH,SOLUONG_MAX,SOLUONG_MIN,VAT,TRANGTHAI)";
                strSQL = strSQL + " values";
                strSQL = strSQL + "(@ID_NSX,@ID_XX,@ID_LOAI,@TEN_MATHANG,@DONVITINH,@SOLUONG_MAX,@SOLUONG_MIN,@VAT,@TRANGTHAI)";
                cmd.CommandText = strSQL;
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@ID_NSX", info.ID_NSX);
                cmd.Parameters.AddWithValue("@ID_XX", info.ID_XX);
                cmd.Parameters.AddWithValue("@ID_LOAI", info.ID_LOAI);
                cmd.Parameters.AddWithValue("@TEN_MATHANG", info.TEN_MATHANG);
                cmd.Parameters.AddWithValue("@DONVITINH", info.DONVITINH);
                cmd.Parameters.AddWithValue("@SOLUONG_MAX", info.SOLUONG_MAX);
                cmd.Parameters.AddWithValue("@SOLUONG_MIN", info.SOLUONG_MIN);
                cmd.Parameters.AddWithValue("@VAT", info.VAT);
                cmd.Parameters.AddWithValue("@TRANGTHAI", info.TRANGTHAI);
                cmd.ExecuteNonQuery();
                provider.CommitTransaction();
            }
            catch (Exception ex)
            {
                errSQL = ex.Message;
                provider.RollbackTransaction();
                //' Neu xay ra loi thi rollback lai
            }
            finally
            {
                //'Dong ket noi
                cmd.Connection.Close();
                cmd.Connection.Dispose();
            }

        }

        public void Update(ref String errConnect, ref String errSQL, hanghoainfo info)
        {
            cmd.Connection = provider.Connection(ref errConnect);
            if (errConnect != "")
            {
                return;
            }
            cmd.Transaction = provider.BeginTransaction();
            try
            {
                SqlParameter f = new SqlParameter();
                f.IsNullable = true;
                f.Value = System.DBNull.Value;
                strSQL = "Update HANGHOA SET  ";
                strSQL = strSQL + "ID_NSX=@ID_NSX,ID_XX=@ID_XX,ID_LOAI=@ID_LOAI,TEN_MATHANG=@TEN_MATHANG,DONVITINH=@DONVITINH,SOLUONG_MAX=@SOLUONG_MAX,SOLUONG_MIN=@SOLUONG_MIN,VAT=@VAT Where  ID_MATHANG = @ID_MATHANG";
                cmd.CommandText = strSQL;

                cmd.Parameters.AddWithValue("@ID_MATHANG", info.ID_MATHANG);
                cmd.Parameters.AddWithValue("@ID_NSX", info.ID_NSX);
                cmd.Parameters.AddWithValue("@ID_XX", info.ID_XX);
                cmd.Parameters.AddWithValue("@ID_LOAI", info.ID_LOAI);
                cmd.Parameters.AddWithValue("@TEN_MATHANG", info.TEN_MATHANG);
                cmd.Parameters.AddWithValue("@DONVITINH", info.DONVITINH);
                cmd.Parameters.AddWithValue("@SOLUONG_MAX", info.SOLUONG_MAX);
                cmd.Parameters.AddWithValue("@SOLUONG_MIN", info.SOLUONG_MIN);
                cmd.Parameters.AddWithValue("@VAT", info.VAT);               
                cmd.ExecuteNonQuery();
                provider.CommitTransaction();
            }
            catch (Exception ex)
            {
                errSQL = ex.Message;
                provider.RollbackTransaction();
                //' Neu xay ra loi thi rollback lai
            }
            finally
            {
                //'Dong ket noi
                cmd.Connection.Close();
                cmd.Connection.Dispose();
            }

        }

        public void Delete(ref String errConnect, ref String errSQL, int id)
        {            
            cmd.Connection = provider.Connection(ref errConnect);
            if (errConnect != "")
            {
                return;
            }
            cmd.Transaction = provider.BeginTransaction();
            try
            {
                strSQL = "DELETE FROM HANGHOA WHERE ID_MATHANG=@RowID";
                cmd.Parameters.AddWithValue("@RowID", id);
                cmd.CommandText = strSQL;
                cmd.ExecuteNonQuery();
                provider.CommitTransaction();
                //' Thuc hien transaction 
            }
            catch (Exception ex)
            {
                errSQL = ex.Message;
                provider.RollbackTransaction();
                //' Neu xay ra loi thi rollback lai
            }
            finally
            {
                //'Dong ket noi
                cmd.Connection.Close();
                cmd.Connection.Dispose();
            }
        }
    }
}
